package imse.team0.inventoryws.db.dal;

import org.hsqldb.jdbc.JDBCDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.concurrent.Semaphore;

/**
 * @author Eugen, 4/28/12 11:54 PM
 */

/**
 * Performs basic queries against the Warehouse Inventory database
 */
public class InventoryQueryService {
    private static DataSource ds;
    private final static String INVENTORYDB_LOCATION = "localhost";
    private final static Connection[] connectionPool = new Connection[100];
    private final static Semaphore[] connectionLock = new Semaphore[connectionPool.length];
    private static Random random = new Random();

    static {
        JDBCDataSource dataSource = new JDBCDataSource();
        dataSource.setUrl("jdbc:hsqldb:hsql://" + INVENTORYDB_LOCATION + "/inventory");
        dataSource.setUser("TEAM0");
        dataSource.setPassword("cloudproject");
        ds = dataSource;
        for (int i = 0; i < connectionPool.length; i++) {
            try {
                connectionPool[i] = ds.getConnection();
            } catch (SQLException e) {
                System.out.println("Global Inventory: Couldn't get connection #" + i);
                e.printStackTrace();
            }
        }
        for (int i = 0; i < connectionPool.length; i++) {
            connectionLock[i] = new Semaphore(1);
        }
        Runtime.getRuntime().addShutdownHook(new Thread(new Runnable() {
            public void run() {
                try {
                    for (int i = 0; i < connectionPool.length; i++) {
                        connectionPool[i].close();
                    }
                } catch (SQLException ex) {
                    System.out.println(ex);
                }
                for (int i = 0; i < connectionLock.length; i++)
                {
                    connectionLock[i].release();
                }
            }
        }));
    }

    private static class StatementRepository {
        private static final String selectOneItem = "SELECT * FROM cloudproject.inventory WHERE id = ?";
        private static final String decreaseQuantity = "UPDATE cloudproject.inventory SET qty = qty - ? where id = ?";
        private static final String increaseQuantity = "UPDATE cloudproject.inventory SET qty = qty + ? where id = ?";

    }

    /**
     * Removes a specified quantity of items from inventory. Actual removal happens only when
     * all items could be removed from inventory, otherwise no item is removed.
     * <p/>
     * Returns how many items couldn't be removed from inventory:
     * 0 - on success
     * requested - available - on failure
     *
     * @param id
     * @param orderedQuantity
     * @return
     * @throws SQLException
     */
    public int removeItem(String id, int orderedQuantity) throws Exception {
        int index = getConnection();
        Connection conn = connectionPool[index];
        try {
            PreparedStatement stmt = conn.prepareStatement(StatementRepository.decreaseQuantity);
            stmt.setInt(1, orderedQuantity);
            stmt.setString(2, id);
            int count = stmt.executeUpdate();
        } catch (SQLIntegrityConstraintViolationException ex) {
            // assuming update failed because of CHECK constraint violation
            PreparedStatement stmt = conn.prepareStatement(StatementRepository.selectOneItem);
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery();
            rs.next();
            int quantity = rs.getInt("qty");
            rs.close();
            return quantity;
        } finally {
            connectionLock[index].release();
        }
        return orderedQuantity;
    }

    /**
     * Puts the back a given quantity for some item id (e.g. during order cancellation events)
     *
     * @param id
     * @param orderedQuantity
     * @throws SQLException
     */
    public void addItem(String id, int orderedQuantity) throws Exception {
        int index = getConnection();
        Connection conn = connectionPool[index];
        try {
            PreparedStatement stmt = conn.prepareStatement(StatementRepository.increaseQuantity);
            stmt.setInt(1, orderedQuantity);
            stmt.setString(2, id);
            int count = stmt.executeUpdate();
        } finally {
            connectionLock[index].release();
        }
        return;
    }

    /**
     * Removes a collection of order items from the inventory.
     *
     * @param ids
     * @param quantities
     * @return If all items are available in the requested quantity, they are all removed from
     *         the inventory and the method returns null. Otherwise, if at least one item wasn't available
     *         all items of this order are put back and the method returns a map of items which caused the order
     *         to be cancelled. Each map entry contains the id of the item and the quantity currently available in the
     *         inventory for that item type
     * @throws SQLException
     */
    public Map<String, Integer> removeItems(List<String> ids, List<Integer> quantities) throws Exception {
        int index = getConnection();
        Connection conn = connectionPool[index];
        PreparedStatement stmt = null;
        Map<String, Integer> availableQTYs = new HashMap<String, Integer>();
        try {
            stmt = conn.prepareStatement(StatementRepository.decreaseQuantity);
            int i = 0;
            for (; i < ids.size(); ++i) {
                stmt.setInt(1, quantities.get(i));
                stmt.setString(2, ids.get(i));
                try {
                    stmt.executeUpdate();
                } catch (SQLIntegrityConstraintViolationException ex) {
                    // check constraint violation
                    break; // break this loop. Undo previous updates and start another loop where we just select the rows and see if they could be supplied in full
                }
            }
            stmt.close();
            if (i == ids.size())// no errors occured
                return null;
            int j = 0;
            for (; j < i; j++) {
                stmt = conn.prepareStatement(StatementRepository.increaseQuantity);
                stmt.setInt(1, quantities.get(j));
                stmt.setString(2, ids.get(j));
                stmt.executeUpdate();
            }
            stmt.close();
            stmt = conn.prepareStatement(StatementRepository.selectOneItem);
            for (; i < ids.size(); i++) {
                stmt.setString(1, ids.get(i));
                ResultSet rs = stmt.executeQuery();
                rs.next();
                int q = rs.getInt("qty");
                if (quantities.get(i) - q > 0)
                    availableQTYs.put(ids.get(i), q);
                rs.close();
            }
        } finally {
            connectionLock[index].release();
        }
        return availableQTYs;
    }

    /**
     * Puts back a given set of order items
     *
     * @param ids
     * @param quantities
     * @throws SQLException
     */
    public void addItems(List<String> ids, List<Integer> quantities) throws Exception {
        int index = getConnection();
        Connection conn = connectionPool[index];
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement(StatementRepository.increaseQuantity);
            int i = 0;
            for (; i < ids.size(); ++i) {
                stmt.setInt(1, quantities.get(i));
                stmt.setString(2, ids.get(i));
                stmt.executeUpdate();
            }
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            connectionLock[index].release();
        }
    }

    private static int getConnection() throws Exception {
        int i = random.nextInt(connectionPool.length);
        try {
            connectionLock[i].acquire();
            if (connectionPool[i].isClosed()) {
                synchronized (InventoryQueryService.class) {
                    if (connectionPool[i].isClosed()) {
                        System.out.println("Global Inventory: Connection " + i + " was closed!");
                        connectionPool[i] = ds.getConnection();
                        return i;
                    }
                }
            }
            return i;
        } catch (Exception ex) {
            connectionLock[i].release();
            throw ex;
        }
    }
}
